﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Data.SqlClient;
using QuanLyBanHang_NewApp.DBAccess;

namespace QuanLyBanHang_NewApp.DAL
{
    public class KhachHangDAL
    {
        dbConnection conn;

        public KhachHangDAL()
        {
            conn = new dbConnection();
        }

        public DataTable selectKH(string ten)
        {
            string query = "select KHACHHANG.*, TienThe from KHACHHANG inner join THETICHDIEM on KHACHHANG.MaThe = THETICHDIEM.MaThe where TenKH like '%'+@ten+'%'";
            SqlParameter[] p = new SqlParameter[1];
            p[0] = new SqlParameter("@ten", ten);
            return conn.execSelectQuery(query, p);
        }

        public bool insertKH(string ten, string dc, string socmt, string sodt, string mathe)
        {
            string query = "insert into KHACHHANG values(@ten,@diachi,@socmt,@sodt,@mathe)";

            SqlParameter[] p = new SqlParameter[5];
            p[0] = new SqlParameter("@ten", ten);
            p[1] = new SqlParameter("@diachi", dc);
            p[2] = new SqlParameter("@socmt", socmt);
            p[3] = new SqlParameter("@sodt", sodt);
            p[4] = new SqlParameter("@mathe", mathe.Trim());

            return conn.execInsertQuery(query, p);
        }

        public bool updateKH(long makh, string ten, string dc, string socmt, string sodt, string mathe)
        {
            string query = "update KHACHHANG set TenKH=@ten, DiaChi=@diachi,SoCMT=@socmt, SoDT=@sodt,MaThe=@mathe where MaKH=@makh";

            SqlParameter[] p = new SqlParameter[6];
            p[0] = new SqlParameter("@ten", ten);
            p[1] = new SqlParameter("@diachi", dc);
            p[2] = new SqlParameter("@socmt", socmt);
            p[3] = new SqlParameter("@sodt", sodt);
            p[4] = new SqlParameter("@mathe", mathe.Trim());
            p[5] = new SqlParameter("@makh", makh);

            return conn.execInsertQuery(query, p);
        }

        public bool deleteKH(long id)
        {
            string query = "delete from KHACHHANG where MaKH =@makh";
            SqlParameter[] p = new SqlParameter[1];
            p[0] = new SqlParameter("@makh", id);
            return conn.execDeleteQuery(query, p);
        }

        public bool insertCard(string mathe, long tienthe)
        {
            string query = "insert into THETICHDIEM values(@mathe,@tienthe)";

            SqlParameter[] p = new SqlParameter[2];
            p[0] = new SqlParameter("@mathe", mathe.Trim());
            p[1] = new SqlParameter("@tienthe", tienthe);

            return conn.execInsertQuery(query, p);
        }

        public bool updateCardInfo(long makh, long tienthe)
        {
            string query = "update THETICHDIEM set TienThe=@tienthe where MaThe=(select MaThe from KHACHHANG where MaKH = @makh)";

            SqlParameter[] p = new SqlParameter[2];
            p[0] = new SqlParameter("@tienthe", tienthe);
            p[1] = new SqlParameter("@makh", makh);

            return conn.execInsertQuery(query, p);
        }

        public bool deleteCard(string id)
        {
            string query = "delete from THETICHDIEM where MaThe=@mathe";
            SqlParameter[] p = new SqlParameter[1];
            p[0] = new SqlParameter("@mathe", id.Trim());
            return conn.execDeleteQuery(query, p);
        }

        public DataTable selectByCardID(string mathe)
        {
            string query = "select * from KHACHHANG inner join THETICHDIEM on KHACHHANG.MaThe = THETICHDIEM.MaThe where KHACHHANG.MaThe=@mathe";
            SqlParameter[] p = new SqlParameter[1];
            p[0] = new SqlParameter("@mathe", mathe.Trim());
            return conn.execSelectQuery(query, p);
        }

    }
}
